﻿'/**********************************************************************
'/// <summary>
'// Name of File     :   UploadEDS.vb
'// Author           :   Sukhminder Singh
'// Create date      :   15th Nov 2011
'/// </summary>
'************************************************************************/

Imports Infragistics.Win
Imports Infragistics.Win.UltraWinGrid
Imports Crystal.CLL
Imports Crystal.CustomControls
Imports CrystalDataHub.View
Imports System.ComponentModel
Imports System
Imports CrystalDataHub.BLL
Imports CrystalDataHub.BLL.CrystalDataHub.BLL
Imports Crystal.CommonDBManagement
Imports Crystal.CLL.CommonCLL

Public Class UploadeSource
    Inherits BaseForm

#Region "Fields"
    Public menmScreenType As DataHubScreenType
    Dim selectedCell As UltraGridCell = Nothing
    Private mstrSelectProject As String = MessageManager.GetMessage("SELECT_PROJECT")
    Private mstrSelectListing As String = MessageManager.GetMessage("SELECT_LISTING")
    Private mstrNoListing As String = MessageManager.GetMessage("NO_LISTING")
    Private mclsUploadeSourceManager As New UploadeSourceManager
    Private _SheetName As String
    Private internalWorkBook As Infragistics.Documents.Excel.Workbook = Nothing
    'Private strConnectionString = CommonCLL.mstrConnectionstring
    Private mintActiveProjectIndex As Integer
    Private mstrScreenName As String = "Upload EDS screen"
    Private mintProjectID As Integer = 0
    Private marrCreateNewFields() As String = {"Fund Type", "Investor Type", "Master Investor Type", "Head Investor", "Style", "Turnover", "City", "State", "Country Name"}

#End Region

#Region "Constructors"

    Public Sub New()
        InitializeComponent()
        'Load += ListUsers_Load;
        'rdAll.Checked = true;
        'UploadeSourcePresenter = New UploadeSourcePresenter(Me)
    End Sub

#End Region

#Region "On Load"

    Private Sub UploadEDS_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            SetControlProperties()
            SetDefaulSearchText()
            InitProjectYear()
            InitCombo()
            GetClientsList()
            Application.DoEvents()
            'grdClients.Rows(1).Selected = True
            'grdClients.Rows(1).Activated = True
            'btnClientNext.PerformClick()
            'btnProjectNext.PerformClick()

            'If DirectCast(cmbeSourceType.DataSource, DataTable).Rows.Count > 1 Then
            '    cmbeSourceType.SelectedIndex = 0
            'End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        End Try
    End Sub

    Private Sub SetControlProperties()
        DoGlobalSettings()
        Me.MaximizeBox = False
        Me.Height = 700
        Me.Location = New Point(50, 10)
        lblNoListing.Visible = False
        'Me.MaximumSize = New Drawing.Size(Me.Width, 1000)
        'Me.WindowState = FormWindowState.Maximized
        If menmScreenType = DataHubScreenType.DataEntry Then
            Me.Text = TitleManager.GetTitle("UploadDE")
        ElseIf menmScreenType = DataHubScreenType.FinalizeList Then
            Me.Text = TitleManager.GetTitle("UploadFL")
        Else
            Me.Text = TitleManager.GetTitle("UploadEDS")
        End If
        'Me.Text = TitleManager.GetTitle("UploadEDS")
        Me.Width = pnlClient.Width + 5
        lblMatchColumns.Visible = False
        If menmScreenType = DataHubScreenType.DataEntry OrElse menmScreenType = DataHubScreenType.FinalizeList Then
            lblSelectListing.Visible = False
            lblProjectName.Visible = False
            lblProjectStatus.Visible = False
            grdListings.Visible = False
            btnIncomplete.Visible = False
            btnProjectNext.Text = "Load"
            grdProjects.Height = grdClients.Height
            Me.MaximumSize = New Size(pnlClient.Width + pnlProjects.Width + 15, Me.Height)
        End If

        pnlProjects.Visible = False
        pnlSelectESource.Visible = False
        Dim Values(2) As Integer

        If CommonCustomControls.IsValidString(StyleManager.GetStyle("ButtonBackColor"), Values) Then
            btnIncomplete.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            pnlAttach.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
        End If
    End Sub

    Private Sub SetGridProperties(ByVal CrystalGrid As UltraGrid)
        Dim lstrQuery As String
        Dim larrData() As DataRow
        Dim lintIndex As Integer
        Try
            CrystalGrid.DisplayLayout.Bands(0).ColHeadersVisible = False

            If CrystalGrid.DisplayLayout.Bands(0).Columns.Exists("IncompleteCount") Then
                CrystalGrid.DisplayLayout.Bands(0).Columns("IncompleteCount").Hidden = True
            End If
            If CrystalGrid.DisplayLayout.Bands(0).Columns.Exists("ClientID") Then
                CrystalGrid.DisplayLayout.Bands(0).Columns("ClientID").Hidden = True
            End If
            If CrystalGrid.DisplayLayout.Bands(0).Columns.Exists("ProjectID") Then
                CrystalGrid.DisplayLayout.Bands(0).Columns("ProjectID").Hidden = True
            End If
            If CrystalGrid.DisplayLayout.Bands(0).Columns.Exists("ProjectName") Then
                CrystalGrid.DisplayLayout.Bands(0).Columns("ProjectName").Width = 110
            End If
            If CrystalGrid.DisplayLayout.Bands(0).Columns.Exists("ListingID") Then
                CrystalGrid.DisplayLayout.Bands(0).Columns("ListingID").Hidden = True
            End If
            If CrystalGrid.DisplayLayout.Bands(0).Columns.Exists("ProjectStatus") Then
                CrystalGrid.DisplayLayout.Bands(0).Columns("ProjectStatus").CellAppearance.TextHAlign = HAlign.Right
            End If
            If CrystalGrid.DisplayLayout.Bands(0).Columns.Exists("ProjectStatusCode") Then
                CrystalGrid.DisplayLayout.Bands(0).Columns("ProjectStatusCode").Hidden = True
            End If
            CrystalGrid.DisplayLayout.Override.BorderStyleRow = UIElementBorderStyle.None
            If CrystalGrid.Name <> grdListings.Name Then
                CrystalGrid.DisplayLayout.Override.MinRowHeight = 30
                CrystalGrid.DisplayLayout.Override.DefaultRowHeight = 30
            End If
            Dim Values(2) As Integer
            If CommonCustomControls.IsValidString(StyleManager.GetStyle("EDSClientBackColor"), Values) Then
                CrystalGrid.DisplayLayout.Override.ActiveCellAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
                CrystalGrid.DisplayLayout.Override.SelectedCellAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
                CrystalGrid.DisplayLayout.Override.ActiveRowAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
                CrystalGrid.DisplayLayout.Override.SelectedRowAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            End If

            If CommonCustomControls.IsValidString(StyleManager.GetStyle("AppThemeBackColor"), Values) Then
                CrystalGrid.DisplayLayout.Override.RowAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
                CrystalGrid.DisplayLayout.Override.RowAlternateAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
                CrystalGrid.DisplayLayout.Appearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            End If

            If CommonCustomControls.IsValidString(StyleManager.GetStyle("AppThemeForeColor"), Values) Then
                CrystalGrid.DisplayLayout.Override.RowAppearance.ForeColor = Color.FromArgb(Values(0), Values(1), Values(2))
                CrystalGrid.DisplayLayout.Override.RowAlternateAppearance.ForeColor = Color.FromArgb(Values(0), Values(1), Values(2))
            End If

            If CommonCustomControls.IsValidString(StyleManager.GetStyle("GridNonSelRowForeColor"), Values) Then
                CrystalGrid.DisplayLayout.Override.SelectedRowAppearance.ForeColor = Color.FromArgb(Values(0), Values(1), Values(2))
                CrystalGrid.DisplayLayout.Override.ActiveRowAppearance.ForeColor = Color.FromArgb(Values(0), Values(1), Values(2))
            End If
            If CrystalGrid.Name = "grdProjects" Then

                lstrQuery = "ProjectStatusCode='" + Crystal.CLL.ProjectStatus.CNCLD.ToString + "'"
                larrData = CType(grdProjects.DataSource, DataTable).Select(lstrQuery)
                For Each lRow As DataRow In larrData
                    lintIndex = CType(grdProjects.DataSource, DataTable).Rows.IndexOf(lRow)
                    grdProjects.Rows(lintIndex).Appearance.BackColor = Color.Gray
                Next
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            lstrQuery = Nothing
            larrData = Nothing
            lintIndex = Nothing
        End Try
    End Sub

    Private Sub DoGlobalSettings()
        'CommonCLL.UserID = 2
        'CommonCLL.UserFullName = "Amit Sanghvi"
        If Not System.Configuration.ConfigurationManager.AppSettings("DataFolder") Is Nothing Then
            mdlDataHubBLLCommon.mstrDataFolder = System.Configuration.ConfigurationManager.AppSettings("DataFolder").ToString
            If System.IO.Directory.Exists(mdlDataHubBLLCommon.mstrDataFolder) = False Then
                System.IO.Directory.CreateDirectory(mdlDataHubBLLCommon.mstrDataFolder)
            End If
        End If
        If Not System.Configuration.ConfigurationManager.AppSettings("IncompleteESource") Is Nothing Then
            mdlDataHubBLLCommon.mstrEDSSnapshotPath = System.Configuration.ConfigurationManager.AppSettings("IncompleteESource").ToString
            If System.IO.Directory.Exists(mdlDataHubBLLCommon.mstrEDSSnapshotPath) = False Then
                System.IO.Directory.CreateDirectory(mdlDataHubBLLCommon.mstrEDSSnapshotPath)
            End If
        End If

        If Not System.Configuration.ConfigurationManager.AppSettings("ManagedFundSuffix") Is Nothing Then
            mdlDataHubBLLCommon.mstrManagedFundSuffix = System.Configuration.ConfigurationManager.AppSettings("ManagedFundSuffix").ToString
            'If System.IO.Directory.Exists(mdlDataHubBLLCommon.mstrManagedFundSuffix) = False Then
            '    System.IO.Directory.CreateDirectory(mdlDataHubBLLCommon.mstrManagedFundSuffix)
            'End If
        End If

        If System.Configuration.ConfigurationManager.AppSettings("MinSearchLength") Is Nothing Then
            mdlDataHubBLLCommon.mintMinSearchLength = 10
        Else
            Dim lblnIsValidInt As Boolean = False
            Integer.TryParse(System.Configuration.ConfigurationManager.AppSettings("MinSearchLength").ToString, lblnIsValidInt)
            If lblnIsValidInt Then
                mdlDataHubBLLCommon.mintMinSearchLength = System.Configuration.ConfigurationManager.AppSettings("MinSearchLength").ToString
            Else
                mdlDataHubBLLCommon.mintMinSearchLength = 10
            End If
        End If

        If System.Configuration.ConfigurationManager.AppSettings("InvestorMinSearchPercentage") Is Nothing Then
            mdlDataHubBLLCommon.mintInvestorMinSearchPercentage = 30
        Else
            Dim lblnIsValidInt As Boolean = False
            Integer.TryParse(System.Configuration.ConfigurationManager.AppSettings("InvestorMinSearchPercentage").ToString, lblnIsValidInt)
            If lblnIsValidInt Then
                mdlDataHubBLLCommon.mintInvestorMinSearchPercentage = System.Configuration.ConfigurationManager.AppSettings("InvestorMinSearchPercentage").ToString
            Else
                mdlDataHubBLLCommon.mintInvestorMinSearchPercentage = 30
            End If
        End If

        If System.Configuration.ConfigurationManager.AppSettings("FundMinSearchPercentage") Is Nothing Then
            mdlDataHubBLLCommon.mintFundMinSearchPercentage = 30
        Else
            Dim lblnIsValidInt As Boolean = False
            Integer.TryParse(System.Configuration.ConfigurationManager.AppSettings("FundMinSearchPercentage").ToString, lblnIsValidInt)
            If lblnIsValidInt Then
                mdlDataHubBLLCommon.mintFundMinSearchPercentage = System.Configuration.ConfigurationManager.AppSettings("FundMinSearchPercentage").ToString
            Else
                mdlDataHubBLLCommon.mintFundMinSearchPercentage = 30
            End If
        End If

        If System.Configuration.ConfigurationManager.AppSettings("InvestorDefaultSearchPercentage") Is Nothing Then
            mdlDataHubBLLCommon.mintInvestorDefaultSearchPercentage = 75
        Else
            Dim lblnIsValidInt As Boolean = False
            Integer.TryParse(System.Configuration.ConfigurationManager.AppSettings("InvestorDefaultSearchPercentage").ToString, lblnIsValidInt)
            If lblnIsValidInt Then
                mdlDataHubBLLCommon.mintInvestorDefaultSearchPercentage = System.Configuration.ConfigurationManager.AppSettings("InvestorDefaultSearchPercentage").ToString
            Else
                mdlDataHubBLLCommon.mintInvestorDefaultSearchPercentage = 75
            End If
        End If

        If System.Configuration.ConfigurationManager.AppSettings("FundDefaultSearchPercentage") Is Nothing Then
            mdlDataHubBLLCommon.mintFundDefaultSearchPercentage = 30
        Else
            Dim lblnIsValidInt As Boolean = False
            Integer.TryParse(System.Configuration.ConfigurationManager.AppSettings("FundDefaultSearchPercentage").ToString, lblnIsValidInt)
            If lblnIsValidInt Then
                mdlDataHubBLLCommon.mintFundDefaultSearchPercentage = System.Configuration.ConfigurationManager.AppSettings("FundDefaultSearchPercentage").ToString
            Else
                mdlDataHubBLLCommon.mintFundDefaultSearchPercentage = 30
            End If
        End If

        If Not System.Configuration.ConfigurationManager.AppSettings("DESnapshotPath") Is Nothing Then
            mdlDataHubBLLCommon.mstrDESnapshotPath = System.Configuration.ConfigurationManager.AppSettings("DESnapshotPath").ToString
            If System.IO.Directory.Exists(mdlDataHubBLLCommon.mstrDESnapshotPath) = False Then
                System.IO.Directory.CreateDirectory(mdlDataHubBLLCommon.mstrDESnapshotPath)
            End If
        End If

        If System.Configuration.ConfigurationManager.AppSettings("SnapshotDemiliter") Is Nothing Then
            mdlDataHubBLLCommon.mstrSnapshotDemiliter = "|"
        Else
            mdlDataHubBLLCommon.mstrSnapshotDemiliter = System.Configuration.ConfigurationManager.AppSettings("SnapshotDemiliter").ToString
        End If

        If Not System.Configuration.ConfigurationManager.AppSettings("IncompleteESourceArchieve") Is Nothing Then
            mdlDataHubBLLCommon.mstrEDSSnapshotPathArchieve = System.Configuration.ConfigurationManager.AppSettings("IncompleteESourceArchieve").ToString
            If System.IO.Directory.Exists(mdlDataHubBLLCommon.mstrEDSSnapshotPathArchieve) = False Then
                System.IO.Directory.CreateDirectory(mdlDataHubBLLCommon.mstrEDSSnapshotPathArchieve)
            End If
        End If

        If Not System.Configuration.ConfigurationManager.AppSettings("DESnapshotPathArchieve") Is Nothing Then
            mdlDataHubBLLCommon.mstrDESnapshotPathArchieve = System.Configuration.ConfigurationManager.AppSettings("DESnapshotPathArchieve").ToString
            If System.IO.Directory.Exists(mdlDataHubBLLCommon.mstrDESnapshotPathArchieve) = False Then
                System.IO.Directory.CreateDirectory(mdlDataHubBLLCommon.mstrDESnapshotPathArchieve)
            End If
        End If
    End Sub

#End Region

#Region "Init"

    ''' <summary>
    ''' To Initilize the combo boxes
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub InitProjectYear()
        With cmbProjectYear
            .ValueMember = "DeliveryDate"
            .DisplayMember = "DeliveryDate"
        End With
    End Sub

    ''' <summary>
    ''' To Initilize the combo boxes
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub InitCombo()
        With cmbeSourceType
            .ValueMember = "SourceTypeCode"
            .DisplayMember = "SourceTypeName"
        End With

        With cmbSheets
            .ValueMember = "SNo."
            .DisplayMember = "Name"
        End With

        With cmbSourceName
            .ValueMember = "SourceID"
            .DisplayMember = "Source Name"
        End With
    End Sub

    ''' <summary>
    ''' To Set various properties(Visibility, Enableness) of controls on load
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetControls()
        Try
            'Me.Height = _MeHeightWithoutStat + 30
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

#End Region

#Region "Events"

#Region "Grid Events"

    Private Sub grdClients_DoubleClickRow(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.DoubleClickRowEventArgs) Handles grdClients.DoubleClickRow
        btnClientNext.PerformClick()
    End Sub

    Private Sub grdClients_AfterRowActivate(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles grdClients.AfterRowActivate
        'Revert to selections to default on client selection change
        cmbProjectYear.DataSource = Nothing
        grdProjects.DataSource = Nothing
        grdListings.DataSource = Nothing
        lblProjectName.Visible = False
        lblProjectStatus.Visible = False
        lblNoProject.Text = mstrSelectProject
        lblNoListing.Text = mstrSelectListing
        SetUploadButtonAndColumnTitle()
    End Sub

    Private Sub grdProjects_DoubleClickRow(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.DoubleClickRowEventArgs) Handles grdProjects.DoubleClickRow
        btnProjectNext.PerformClick()
    End Sub

    Private Sub grdProjects_AfterRowActivate(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles grdProjects.AfterRowActivate
        Try
            If grdProjects.ActiveRow IsNot Nothing Then
                mintProjectID = grdProjects.ActiveRow.GetCellValue("ProjectID")
                mintActiveProjectIndex = grdProjects.ActiveRow.Index
                'If the current project is a cancelled project and the last selected project is not a cancelled project then select/activate the last selected project
                If grdProjects.ActiveRow.GetCellValue("ProjectStatusCode").Equals(ProjectStatus.CNCLD.ToString) AndAlso _
                    grdProjects.Rows(mintActiveProjectIndex).GetCellValue("ProjectStatusCode").Equals(ProjectStatus.CNCLD.ToString) = False Then
                    If GetNextValidProject() = False Then
                        grdProjects.Rows(mintActiveProjectIndex).Activated = True
                        Exit Sub
                    End If
                End If
            End If
            If menmScreenType = DataHubScreenType.eSource Then
                btnIncomplete.Visible = False
                lblProjectName.Text = grdProjects.ActiveRow.GetCellValue("ProjectName")
                lblProjectStatus.Text = grdProjects.ActiveRow.GetCellValue("ProjectStatus")
                If lblProjectName.Visible = False Then
                    lblProjectName.Visible = True
                    lblProjectStatus.Visible = True
                End If
                GetListingSummary()
            End If
            SetUploadButtonAndColumnTitle()
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        End Try
    End Sub

    Private Sub grdListings_AfterRowActivate(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles grdListings.AfterRowActivate
        'If there is any incompleted data entity for the selected project, then show the Incomplete button to load.
        If grdListings.ActiveRow.GetCellValue("IncompleteCount") > 0 Then
            btnIncomplete.Visible = True
        Else
            btnIncomplete.Visible = False
        End If
        SetUploadButtonAndColumnTitle()
    End Sub

#End Region

#Region "Form Events"

    Public Sub UploadeSource_FormClosing(ByVal sender As Object, ByVal e As FormClosingEventArgs) Handles MyBase.FormClosing
        mclsUploadeSourceManager = Nothing
    End Sub

    Private Sub UploadeSource_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Me.Dispose()
    End Sub

#End Region

#Region "Drop Down Events"

    Private Sub cmbProjectYear_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbProjectYear.SelectedIndexChanged
        GetProjectSummary()
    End Sub

    Private Sub cmbeSourceType_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbeSourceType.SelectedIndexChanged
        Dim dtSourceNames As DataTable = Nothing
        Try
            dtSourceNames = New DataTable
            dtSourceNames.Load(mclsUploadeSourceManager.GeteSourceNames(cmbeSourceType.SelectedItem("SourceTypeCode")))
            If CommonCLL.CheckNothingDataTable(dtSourceNames) Then Exit Sub
            If CommonCLL.DropDownAtFirst(dtSourceNames, cmbSourceName.ValueMember, cmbSourceName.DisplayMember, CommonCLL.m_strNewIDValue, CommonCLL.m_strNewTextValue) = False Then
                CommonCLL.ShowMessage("Error in accessing Format drop down.", MessageBoxIcons.Error)
                Exit Sub
            End If
            Dim dr As DataRow
            dr = dtSourceNames.NewRow
            dr.Item("SourceID") = ContentManager.GetContent("SELECT_VAL")
            dr.Item("Source Name") = ContentManager.GetContent("SELECT_TEXT")
            dtSourceNames.Rows.InsertAt(dr, 0)
            cmbSourceName.DataSource = dtSourceNames
            AutoSelectSourceName()
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            dtSourceNames = Nothing
        End Try
    End Sub

    Private Sub AutoSelectSourceName()

        Dim dtSourceNames As DataTable = Nothing
        dtSourceNames = New DataTable
        dtSourceNames = cmbSourceName.DataSource
        Dim larrFactsetSourceName() As DataRow = Nothing
        Try
            If dtSourceNames.Rows.Count > 1 Then

                Dim enmSourceTypeCode = DirectCast([Enum].Parse(GetType(SourceTypeCode), cmbeSourceType.SelectedItem("SourceTypeCode")), SourceTypeCode)
                'Dim enmSourceTypeCode = DirectCast(cmbeSourceType.SelectedItem("SourceTypeID"), SourceTypeCode)
                If enmSourceTypeCode = SourceTypeCode.PBC Then
                    larrFactsetSourceName = dtSourceNames.Select("[Source Name]='Factset'")
                    If larrFactsetSourceName.Length = 1 Then
                        cmbSourceName.SelectedIndex = dtSourceNames.Rows.IndexOf(larrFactsetSourceName(0))
                    Else
                        cmbSourceName.SelectedIndex = 0
                    End If
                Else
                    cmbSourceName.SelectedIndex = 0
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            dtSourceNames = Nothing
            larrFactsetSourceName = Nothing
        End Try
    End Sub

    Private Sub cmbSourceName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSourceName.SelectedIndexChanged
        ChangeSourceName()
    End Sub

    ''' <summary>
    '''  'To Get Excel Sheet Data
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub cmbSheets_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSheets.SelectedIndexChanged
        Dim IsColumDupliacte As Boolean = False
        Dim lintCurrentSourceNameIndex As Int16
        Try
            cmbSourceName.Enabled = False
            If cmbSheets.SelectedIndex > 0 Then
                cmbSourceName.Enabled = True
                'btnUpload.Enabled = True
                If cmbSourceName.SelectedIndex <> -1 Then
                    If DirectCast(cmbSourceName.DataSource, DataTable).Rows.Count > 2 Then
                        lintCurrentSourceNameIndex = cmbSourceName.SelectedIndex
                        'cmbSourceName.SelectedIndex = 2
                        AutoSelectSourceName()
                        If lintCurrentSourceNameIndex > 1 Then
                            ChangeSourceName()
                        End If
                    Else
                        cmbSourceName.SelectedIndex = 0
                    End If
                End If
                'If cmbTemplate.SelectedIndex <> -1 Then
                '    If DirectCast(cmbTemplate.DataSource, DataTable).Rows.Count > 0 Then
                '        cmbTemplate.SelectedIndex = 1
                '    Else
                '        cmbTemplate.SelectedIndex = 0
                '    End If
                'End If
            Else
                'btnUpload.Enabled = False
                cmbSourceName.SelectedIndex = 0
            End If
            SetUploadButtonAndColumnTitle()
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        End Try
    End Sub

#End Region

#Region "Button"

    Private Sub btnGetSnapshot_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetSnapshot.Click
        Dim lfrmImporteSource As ImporteSource
        Try
            lfrmImporteSource = New ImporteSource(Nothing, Nothing, 5, mintProjectID)
            If lfrmImporteSource.mstrScreenStatus.Equals("") = False Then
                MessageBox.Show(lfrmImporteSource.mstrScreenStatus, MessageManager.GetMessage("CRYSTAL"), MessageBoxButtons.OK, MessageBoxIcon.Information)
                lfrmImporteSource.Close()
            Else
                lfrmImporteSource.mstrMappedInvName = mstrMappedInvName
                lfrmImporteSource.mstrMappedFundName = mstrMappedFundName
                lfrmImporteSource.marrExcludedFromFilters = marrExcludedFromFilters
                lfrmImporteSource.mintClientID = 5
                lfrmImporteSource.mintProjectID = 15
                lfrmImporteSource.mintListingID = 1
                lfrmImporteSource.mstrClientName = "BNP Paribas"
                lfrmImporteSource.mstrProjectName = "12 December 2011"
                'lfrmImporteSource.mintEntityID = 5
                lfrmImporteSource.mstrSourceTypeCode = "EBL"
                lfrmImporteSource.WindowState = FormWindowState.Maximized
                lfrmImporteSource.menmSnapshotType = SnapshotType.Complete
                lfrmImporteSource.Show()
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            lfrmImporteSource = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To load the incompleted snapshots
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnIncomplete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncomplete.Click
        Dim lfrmListSnapshot As ListSnapshot = Nothing
        Dim lfrmImporteSource As ImporteSource
        Try
            lfrmListSnapshot = New ListSnapshot
            lfrmListSnapshot.mintProjectID = grdProjects.ActiveRow.GetCellValue("ProjectID")
            lfrmListSnapshot.mintListingID = grdListings.ActiveRow.GetCellValue("ListingID")
            lfrmListSnapshot.mintSnapshotEntityID = 0
            lfrmListSnapshot.mblnIsCompleted = False
            lfrmListSnapshot.ShowDialog()
            'If a valid snapshot is selected, then open the mapping screen
            If lfrmListSnapshot.mintSnapshotEntityID <> 0 Then
                lfrmImporteSource = New ImporteSource(Nothing, Nothing, lfrmListSnapshot.mintSnapshotEntityID, grdProjects.ActiveRow.GetCellValue("ProjectID"))
                If lfrmImporteSource.mstrScreenStatus.Equals("") = False Then
                    MessageBox.Show(lfrmImporteSource.mstrScreenStatus, MessageManager.GetMessage("CRYSTAL"), MessageBoxButtons.OK, MessageBoxIcon.Information)
                    lfrmImporteSource.Close()
                Else
                    'Do the pre-opening settings
                    lfrmImporteSource.mstrMappedInvName = mstrMappedInvName
                    lfrmImporteSource.mstrMappedFundName = mstrMappedFundName
                    lfrmImporteSource.marrExcludedFromFilters = marrExcludedFromFilters
                    lfrmImporteSource.mintClientID = grdClients.ActiveRow.GetCellValue("ClientID")
                    lfrmImporteSource.mintProjectID = grdProjects.ActiveRow.GetCellValue("ProjectID")
                    lfrmImporteSource.mintListingID = grdListings.ActiveRow.GetCellValue("ListingID")
                    lfrmImporteSource.mstrClientName = grdClients.ActiveRow.GetCellValue("ClientName")
                    lfrmImporteSource.mstrProjectName = grdProjects.ActiveRow.GetCellValue("ProjectName")
                    lfrmImporteSource.mstrListingName = grdListings.ActiveRow.GetCellValue("ListingName")
                    lfrmImporteSource.mintEntityID = lfrmListSnapshot.mintSnapshotEntityID
                    lfrmImporteSource.mstrSourceName = lfrmListSnapshot.mstrSnapshotSourceName
                    lfrmImporteSource.mstrDataSourceName = lfrmListSnapshot.mstrDataSourceName
                    lfrmImporteSource.mintSourceTypeID = lfrmListSnapshot.mintSnapshotSourceTypeID
                    lfrmImporteSource.mintTemplateID = lfrmListSnapshot.mintSnapshotTemplateID
                    lfrmImporteSource.mstrSnapshotFilePath = lfrmListSnapshot.mstrSnapshotFilePath
                    lfrmImporteSource.mstrSourceTypeCode = lfrmListSnapshot.mstrSourceTypeCode
                    lfrmImporteSource.mintRegularRadio = lfrmListSnapshot.mintRegularRadio
                    lfrmImporteSource.mblnRetail = lfrmListSnapshot.mblnRetail
                    lfrmImporteSource.mblnCustodian = lfrmListSnapshot.mblnCustodian
                    lfrmImporteSource.mblnCreateNew = lfrmListSnapshot.mblnCreateNew
                    lfrmImporteSource.mintAdvancedRadio = lfrmListSnapshot.mintAdvancedRadio
                    lfrmImporteSource.mintRowNumber = lfrmListSnapshot.mintRowNumber
                    lfrmImporteSource.mstrHiddenColumns = lfrmListSnapshot.mstrHiddenColumns
                    lfrmImporteSource.menmSnapshotType = SnapshotType.Incomplete
                    lfrmImporteSource.menmSourceTypeCode = lfrmImporteSource.mintSourceTypeID
                    lfrmImporteSource.WindowState = FormWindowState.Maximized
                    lfrmImporteSource.Show()
                    Me.Close()
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            lfrmListSnapshot = Nothing
            lfrmImporteSource = Nothing
        End Try
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClientNext.Click
        Me.Width = pnlClient.Width + pnlProjects.Width + 15
        pnlProjects.Visible = True
        If grdClients.ActiveRow IsNot Nothing Then
            GetYears()
        End If
    End Sub

    Private Sub btnProjectNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProjectNext.Click
        Dim lstrFormText As String = Nothing
        Dim lblnInstance As Boolean = False
        Dim lfrmDataHub As DataHub
        Dim lintScreenType As Integer
        Dim mclsUploadeSourceManager As New UploadeSourceManager
        Try
            If menmScreenType = DataHubScreenType.eSource Then
                lstrFormText = "Crystal - " + grdClients.ActiveRow.GetCellValue("ClientName") + " - " + grdProjects.ActiveRow.GetCellValue("ProjectName") + " - " + TitleManager.GetTitle("DataHub") + " - Data Entry" + " - Finalise Listing"
                FocusOnExistingInstance(lstrFormText, "UploadeSource", FormWindowState.Maximized, lblnInstance)
                If Not lblnInstance Then
                    pnlSelectESource.Visible = True
                    Me.Width = pnlClient.Width + pnlProjects.Width + pnlSelectESource.Width + 25
                    Dim Values(2) As Integer
                    If CommonCustomControls.IsValidString(StyleManager.GetStyle("ButtonBackColor"), Values) Then
                        pnlAttach.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
                    End If
                    If cmbeSourceType.DataSource Is Nothing Then
                        GeteSourceTypeList()
                    End If
                End If
            Else
                lintScreenType = mclsUploadeSourceManager.CheckOpenHubForm(mintProjectID, UserID) '.InsertDataEntity()
                If lintScreenType = DataHubScreenType.DataEntry Or lintScreenType = DataHubScreenType.eSource Then
                    Me.ShowMessageBox(DBResult.FL_DEUSEDBYUSER)
                    Exit Sub
                ElseIf lintScreenType = DataHubScreenType.FinalizeList Then
                    Me.ShowMessageBox(DBResult.FL_ALRDYOPND_OTHRUSR)
                    Exit Sub
                End If
                'lstrFormText = "Crystal - " + grdClients.ActiveRow.GetCellValue("ClientName") + " - " + grdProjects.ActiveRow.GetCellValue("ProjectName") + " - " + TitleManager.GetTitle("DataHub") + " - Data Entry"
                'CommonCLL.FocusOnExistingInstanceByName("UploadeSource")
                'FocusOnExistingInstance(lstrFormText, "DataHub", FormWindowState.Maximized, lblnInstance)
                If CommonCLL.FocusOnExistingInstanceByName("DataHub") Then
                    lfrmDataHub = New DataHub()
                    lfrmDataHub.mintClientID = grdClients.ActiveRow.GetCellValue("ClientID")
                    lfrmDataHub.mintProjectID = mintProjectID
                    lfrmDataHub.mstrClientName = grdClients.ActiveRow.GetCellValue("ClientName")
                    lfrmDataHub.mstrProjectName = grdProjects.ActiveRow.GetCellValue("ProjectName")
                    lfrmDataHub.menmScreenType = menmScreenType
                    If menmScreenType = DataHubScreenType.DataEntry Then
                        mclsUploadeSourceManager.OpenHubForm(mintProjectID, CommonCLL.UserID, DataHubScreenType.eSource)
                    ElseIf menmScreenType = DataHubScreenType.eSource Then
                        mclsUploadeSourceManager.OpenHubForm(mintProjectID, CommonCLL.UserID, DataHubScreenType.DataEntry)
                    Else
                        mclsUploadeSourceManager.OpenHubForm(mintProjectID, CommonCLL.UserID, DataHubScreenType.FinalizeList)
                    End If
                    lfrmDataHub.Show()
                Else
                    Me.Dispose()
                    Me.Close()
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            lfrmDataHub = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="pintProjectID"></param>
    ''' <param name="pintClientID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CheckOpenFLForm(ByVal pintProjectID As Integer, ByVal pintClientID As Integer) As Integer

    End Function

    ''' <summary>
    ''' Incomplete Back Color
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnIncomplete_BackColorChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncomplete.BackColorChanged
        Dim Values(2) As Integer
        If CommonCustomControls.IsValidString(StyleManager.GetStyle("ButtonBackColor"), Values) Then
            btnIncomplete.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
        End If
    End Sub

    ''' <summary>
    ''' To select eSource Excel file to Import
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnAttach_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAttach.Click
        Dim ObjImportFile As New OpenFileDialog
        'Dim lstrDataFolder As String = ""
        Try
            With ObjImportFile
                .Filter = "Excel (97-2003 format)(*.xls)|*.xls|Excel (2007-2010 format)(*.xlsx)|*.xlsx"
                '.Filter = "Excel (2007-2010 format)(*.xlsx)|*.xlsx|Excel (97-2003 format)(*.xls)|*.xls"
                .Multiselect = False
                .InitialDirectory = mstrDataFolder
                .DefaultExt = ".xlsx"
                If .ShowDialog() <> Windows.Forms.DialogResult.Cancel Then
                    txtAttach.Text = .FileName
                    Application.DoEvents()
                    If (Not CommonCLL.IsEmpty(txtAttach.Text)) AndAlso IO.File.Exists(Trim(txtAttach.Text)) Then
                        If GetWorksheetNames(Trim(txtAttach.Text)) Then
                            cmbSheets.Enabled = True
                        Else
                            'MessageManager.GetMessage("USERDELETED")
                            'Me.ShowMessageBox(DBResult.INVALID_FILE, BaseClass.Capitalize("Upload File"))
                            'MessageBox.Show("Please select a valid file.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                            Exit Sub
                        End If
                    End If
                End If
            End With
            AutoSelectSourceName()
            'If CType(cmbSourceName.DataSource, DataTable).Rows.Count > 1 Then
            '    cmbSourceName.SelectedIndex = 2
            'End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            ObjImportFile = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To open the Import Screen
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Dim ldtExcelData As DataTable = Nothing
        Dim ldtColMapping As DataTable = Nothing
        Dim IsColumDupliacte As Boolean = False
        Dim lintEntityID As Integer
        Dim lintTemplateID As Int16
        Dim lstrColumnTitle As String
        Dim enmResult As DBResult
        Dim lintClientID As Int16
        Dim lfrmImporteSource As ImporteSource
        'Get the Source Type i.e. Public, Banklist, Register etc.
        Dim enmSourceTypeCode = DirectCast([Enum].Parse(GetType(SourceTypeCode), cmbeSourceType.SelectedItem("SourceTypeCode")), SourceTypeCode)
        'Dim enmSourceTypeCode = DirectCast(cmbeSourceType.SelectedItem("SourceTypeID"), SourceTypeCode)
        Try
            ldtExcelData = New DataTable
            'Get the TemplateID, if the Template mapping user control is opened and has a valid selected item
            If Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping") Is Nothing Then
                lintTemplateID = -1
            ElseIf DirectCast(Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping").Controls("pnlUCTemplateMapping").Controls("cmbTemplates"), ComboBox).SelectedItem Is Nothing Then
                lintTemplateID = -1
            Else
                lintTemplateID = DirectCast(Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping").Controls("pnlUCTemplateMapping").Controls("cmbTemplates"), ComboBox).SelectedItem("ETemplateID")
                DirectCast(Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping"), UCTemplateMapping).mblnIsFromUploadEDSScreen = True
                If DirectCast(Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping"), UCTemplateMapping).SaveMapping = False Then
                    Exit Sub
                End If
                'Get the Column Mappping's details in datatable
                ldtColMapping = DirectCast(Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping").Controls("grdFormats"), Crystal.CustomControls.CrystalUltraGrid).DataSource
            End If

            'If some validation is failed, then exit.
            If Not GetExcelFileData(IsColumDupliacte, ldtExcelData, True, ldtColMapping) OrElse cmbSheets.SelectedItem("SNo.").Equals(CommonCLL.m_strSelectIDValue) Then
                Exit Sub
            End If

            'If an invalid source name is selected, then exit.
            If cmbSourceName.SelectedItem("SourceID").Equals(CommonCLL.m_strSelectIDValue) Then
                Me.ShowMessageBox(DBResult.EDS_UPLD_VALD_SOURCE)
                Exit Sub
            End If

            'If an invalid Template is selected, then exit.
            If lintTemplateID = CommonCLL.m_strSelectIDValue Then
                Me.ShowMessageBox(DBResult.EDS_UPLD_VALD_TMPLTE, BaseClass.Capitalize("Template"))
                Exit Sub
            End If

            'If column title contains Parent Data Source Column Name, then exit.
            If String.Compare(txtColumnTitle.Text.Trim, mstrUpdatesColPrefix.Trim, True) = 0 OrElse _
                String.Compare(txtColumnTitle.Text.Trim, mstrDDInfoColPrefix.Trim, True) = 0 OrElse _
                String.Compare(txtColumnTitle.Text.Trim, mstrRegisterColPrefix.Trim, True) = 0 OrElse _
                String.Compare(txtColumnTitle.Text.Trim, mstrBanklistColPrefix.Trim, True) = 0 Then
                Me.ShowMessageBox(DBResult.EDS_SYS_COL_NAME, txtColumnTitle.Text.Trim + " " + lblListingName.Text)
                Exit Sub
            End If

            'If column title contains current listing name, then exit.
            If txtColumnTitle.Text.ToUpper.Contains(" " + lblListingName.Text.ToUpper) Then
                Me.ShowMessageBox(DBResult.EDS_UPLD_CLMN_TITLE_LSTNGNAME)
                Exit Sub
            End If

            'Call the procedure to set the index/ordinal of position column
            If enmSourceTypeCode = SourceTypeCode.DDI OrElse enmSourceTypeCode = SourceTypeCode.EBL OrElse enmSourceTypeCode = SourceTypeCode.REG OrElse enmSourceTypeCode = SourceTypeCode.PBC Then
                Dim lintOrdinal As Integer = -1
                Dim lstrColName As String = ""
                GetMappedPositionOrdinal(ldtExcelData, ldtColMapping, lintOrdinal, lstrColName)
            End If

            'Insert data entity in the database
            lstrColumnTitle = txtColumnTitle.Text.Trim + " " + lblListingName.Text
            enmResult = DBResult.SUCCESS
            'cmbSourceName
            mclsUploadeSourceManager.InsertDataEntity(cmbSourceName.SelectedItem("SourceID"), lstrColumnTitle, grdListings.ActiveRow.GetCellValue("ListingID"), grdProjects.ActiveRow.GetCellValue("ProjectID"), CommonCLL.UserID, lintEntityID, enmResult, dtSourceDate.Value)
            'lintEntityID = 5
            If enmResult = DBResult.FAILED Then
                Me.ShowMessageBox(DBResult.FAILED, lstrColumnTitle)
            ElseIf enmResult = DBResult.EXIST Then
                Me.ShowMessageBox(DBResult.EXIST, lstrColumnTitle)
            ElseIf enmResult = DBResult.SUCCESS Then
                If ldtExcelData.Rows.Count > 0 Then
                    'Add columns that will be used in staging table for mappings in Import EDS screen
                    ldtExcelData.Columns.Add("EntityID", GetType(Integer))
                    ldtExcelData.Columns.Add("ETemplateID", GetType(Int16))
                    ldtExcelData.Columns.Add("ClientID", GetType(Int16))
                    ldtExcelData.Columns.Add("RowNumber", GetType(Integer))

                    lintClientID = grdClients.ActiveRow.GetCellValue("ClientID")
                    For Each lRow As DataRow In ldtExcelData.Rows
                        lRow.Item("RowNumber") = ldtExcelData.Rows.IndexOf(lRow)
                        lRow.Item("EntityID") = lintEntityID
                        'lRow.Item("EntityID") = lintEntityID
                        lRow.Item("ETemplateID") = lintTemplateID
                        lRow.Item("ClientID") = lintClientID
                    Next

                    'If the selected source type is Public, then add PublicMatch column in the Column Mapping table that will be used instead of DBFieldName column
                    If enmSourceTypeCode = SourceTypeCode.PBC Then
                        SetColMappingPublicMatch(ldtColMapping)
                        CType(Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping").Controls("grdFormats"), Crystal.CustomControls.CrystalUltraGrid).Rows.Band.Columns("PublicMatch").Hidden = True
                    End If
                    lfrmImporteSource = New ImporteSource(ldtExcelData, ldtColMapping, lintEntityID, grdProjects.ActiveRow.GetCellValue("ProjectID"))
                    'Dim lintTemplateID As Int16 = DirectCast(Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping").Controls("pnlUCTemplateMapping").Controls("cmbTemplates"), ComboBox).SelectedItem("ETemplateID")

                    'If the mapping screen for the selected project is already opened, then inform the user. Else open it.
                    If lfrmImporteSource.mstrScreenStatus.Equals("") = False Then
                        MessageBox.Show(lfrmImporteSource.mstrScreenStatus, MessageManager.GetMessage("CRYSTAL"), MessageBoxButtons.OK, MessageBoxIcon.Information)
                        lfrmImporteSource.Close()
                    Else
                        lfrmImporteSource.mstrMappedInvName = mstrMappedInvName
                        lfrmImporteSource.mstrMappedFundName = mstrMappedFundName
                        lfrmImporteSource.marrExcludedFromFilters = marrExcludedFromFilters
                        lfrmImporteSource.mintClientID = grdClients.ActiveRow.GetCellValue("ClientID")
                        lfrmImporteSource.mintTemplateID = lintTemplateID
                        'lfrmImporteSource.mstrSourceName = txtColumnTitle.Text.Trim
                        lfrmImporteSource.mstrSourceName = lstrColumnTitle
                        lfrmImporteSource.mstrDataSourceName = cmbSourceName.SelectedItem("Source Name")
                        lfrmImporteSource.mintSourceTypeID = cmbeSourceType.SelectedItem("SourceTypeID")
                        'lfrmImporteSource.mintSourceTypeID = 4
                        lfrmImporteSource.mintProjectID = grdProjects.ActiveRow.GetCellValue("ProjectID")
                        lfrmImporteSource.mintListingID = grdListings.ActiveRow.GetCellValue("ListingID")
                        'lfrmImporteSource.mintEntityID = 5
                        lfrmImporteSource.mstrClientName = grdClients.ActiveRow.GetCellValue("ClientName")
                        lfrmImporteSource.mstrProjectName = grdProjects.ActiveRow.GetCellValue("ProjectName")
                        lfrmImporteSource.mstrListingName = grdListings.ActiveRow.GetCellValue("ListingName")
                        'lfrmImporteSource.mintUserID = mintUserID
                        lfrmImporteSource.menmSnapshotType = SnapshotType.NoSnapshot
                        lfrmImporteSource.menmSourceTypeCode = enmSourceTypeCode
                        lfrmImporteSource.WindowState = FormWindowState.Maximized
                        lfrmImporteSource.Show()
                        Me.Close()
                    End If
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            ldtExcelData = Nothing
            ldtColMapping = Nothing
        End Try
    End Sub
#End Region

#Region "Text Box"

    ''' <summary>
    ''' To filter the clients on the basis of typed text
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
        If MessageManager.GetMessage("SEARCHTEXT") = txtSearch.Text Then Exit Sub
        If grdClients.DataSource IsNot Nothing Then
            If grdClients.Rows.Band.Columns.Exists("ClientName") Then
                grdClients.Rows.ColumnFilters("ClientName").FilterConditions(0).CompareValue = txtSearch.Text
                grdClients.DisplayLayout.RefreshFilters()
            End If
        End If
    End Sub

    Private Sub txtSearchUser_MouseClick(ByVal sender As Object, ByVal e As MouseEventArgs) Handles txtSearch.MouseClick
        If MessageManager.GetMessage("SEARCHTEXT") = txtSearch.Text Then
            txtSearch.Text = String.Empty
        End If
    End Sub

    Private Sub txtSearch_Leave(ByVal sender As Object, ByVal e As EventArgs) Handles txtSearch.Leave
        If String.IsNullOrEmpty(txtSearch.Text.Trim()) Then
            SetDefaulSearchText()
        End If
    End Sub

#End Region

#End Region

#Region "Procedures"

    ''' <summary>
    ''' Set the Upload button and Column Title based on the current selection of the controls
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetUploadButtonAndColumnTitle()
        Dim lblnIsEnable As Boolean = True
        If grdClients.ActiveRow Is Nothing Then
            lblnIsEnable = False
        End If
        If grdProjects.ActiveRow Is Nothing Then
            lblnIsEnable = False
        End If
        If grdListings.ActiveRow Is Nothing Then
            lblnIsEnable = False
        End If
        If cmbSheets.SelectedItem Is Nothing Then
            lblnIsEnable = False
        ElseIf cmbSheets.SelectedItem("SNo.") = CommonCLL.m_strSelectIDValue Then
            lblnIsEnable = False
        End If
        If cmbSheets.SelectedItem Is Nothing Then
            lblnIsEnable = False
        ElseIf cmbSourceName.SelectedItem Is Nothing Then
            lblnIsEnable = False
        ElseIf cmbSourceName.SelectedItem("SourceID") = CommonCLL.m_strSelectIDValue OrElse cmbSourceName.SelectedItem("SourceID") = CommonCLL.m_strNewIDValue Then
            lblnIsEnable = False
        End If
        If Me.Controls("pnlSelectESource").Controls("pnlTemplateMapping").Controls("UCTemplateMapping") Is Nothing Then
            lblnIsEnable = False
        End If

        If lblnIsEnable Then
            btnUpload.Enabled = True
            FormulateColumnTitle()
        Else
            lblListingName.Text = ""
            txtColumnTitle.Text = ""
            btnUpload.Enabled = False
        End If
    End Sub

    ''' <summary>
    ''' To do the settings on change of the selected source
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ChangeSourceName()
        Dim objAddValue As CrystalCommon.View.AddValue
        Dim dsReturn As DataSet = Nothing
        Try
            'FormulateColumnTitle()
            If cmbSourceName.SelectedItem("SourceID") = CommonCLL.m_strNewIDValue Then
                objAddValue = New CrystalCommon.View.AddValue(enmEntityType.Source.ToString(), LKPMaster.EDSDSN, "Source Name")
                objAddValue.StartPosition = FormStartPosition.CenterScreen
                objAddValue.ParameterInt = cmbeSourceType.SelectedItem("SourceTypeID")
                objAddValue.ShowDialog()

                If objAddValue.ID <> String.Empty Then
                    If AddRowInDropDownAtLast(cmbSourceName.DataSource, cmbSourceName.ValueMember, cmbSourceName.DisplayMember, objAddValue.ID, objAddValue.mstrAddedValue) = False Then
                        CommonCLL.ShowMessage("Error in accessing Source Name drop down.", MessageBoxIcons.Error)
                        Exit Sub
                    End If
                    cmbSourceName.SelectedValue = objAddValue.ID
                End If
            End If
            GetTemplateMapping(False)
            SetUploadButtonAndColumnTitle()
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            objAddValue = Nothing
            dsReturn = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To set the default text of Search text box
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub SetDefaulSearchText()
        txtSearch.Text = MessageManager.GetMessage("SEARCHTEXT")
    End Sub

    ''' <summary>
    ''' To get the list of the clients
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub GetClientsList()
        Dim ldtClientList As DataTable = Nothing
        Try
            ldtClientList = New DataTable
            ldtClientList.Load(mclsUploadeSourceManager.GetClientList())
            grdClients.DataSource = ldtClientList
            grdClients.DataBind()
            grdClients.Rows.Refresh(RefreshRow.ReloadData)
            SetGridProperties(grdClients)
            'To add the filter
            grdClients.Rows.ColumnFilters("ClientName").FilterConditions.Add(Infragistics.Win.UltraWinGrid.FilterComparisionOperator.Contains, "")
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            ldtClientList = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To get the project years of the selected client
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub GetYears()
        Dim ldtProjectYear As DataTable = Nothing
        Try
            ldtProjectYear = New DataTable
            ldtProjectYear.Load(mclsUploadeSourceManager.GetProjectYear(grdClients.ActiveRow.GetCellValue("ClientID")))
            InitProjectYear()
            cmbProjectYear.DataSource = ldtProjectYear
            If ldtProjectYear IsNot Nothing Then
                If ldtProjectYear.Rows.Count > 0 Then
                    cmbProjectYear.SelectedIndex = 0
                Else
                    lblNoListing.Text = mstrNoListing
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            ldtProjectYear = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To get the project summary/delivery date of the selected year
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub GetProjectSummary()
        Dim ldtProjectSummary As DataTable = Nothing
        Dim larrProjectSummary() As DataRow
        Dim lstrQuery As String
        Dim lintSelectedRowIndex As Integer
        Try
            ldtProjectSummary = New DataTable
            If cmbProjectYear.SelectedItem IsNot Nothing Then
                ldtProjectSummary.Load(mclsUploadeSourceManager.GetProjectSummary(grdClients.ActiveRow.GetCellValue("ClientID"), cmbProjectYear.SelectedItem("DeliveryDate")))
            End If
            If ldtProjectSummary.Rows.Count > 0 Then
                grdProjects.Visible = True
                lblNoProject.Visible = False
                grdProjects.DataSource = ldtProjectSummary

                'Select a project the priority sequence for selection is : DE Sent, Finalize Listing, DR Sent, DR Pending, Completed, Cancelled.
                lstrQuery = "ProjectStatusCode ='" + ProjectStatus.DESRT.ToString + "'"
                larrProjectSummary = ldtProjectSummary.Select(lstrQuery)
                If larrProjectSummary.Length > 0 Then
                    lintSelectedRowIndex = ldtProjectSummary.Rows().IndexOf(larrProjectSummary(0))
                Else
                    lstrQuery = "ProjectStatusCode ='" + ProjectStatus.FNLST.ToString + "'"
                    larrProjectSummary = ldtProjectSummary.Select(lstrQuery)
                    If larrProjectSummary.Length > 0 Then
                        lintSelectedRowIndex = ldtProjectSummary.Rows().IndexOf(larrProjectSummary(0))
                    Else
                        lstrQuery = "ProjectStatusCode ='" + ProjectStatus.DRSNT.ToString + "'"
                        larrProjectSummary = ldtProjectSummary.Select(lstrQuery)
                        If larrProjectSummary.Length > 0 Then
                            lintSelectedRowIndex = ldtProjectSummary.Rows().IndexOf(larrProjectSummary(0))
                        Else
                            lstrQuery = "ProjectStatusCode ='" + ProjectStatus.DRPND.ToString + "'"
                            larrProjectSummary = ldtProjectSummary.Select(lstrQuery)
                            If larrProjectSummary.Length > 0 Then
                                lintSelectedRowIndex = ldtProjectSummary.Rows().IndexOf(larrProjectSummary(0))
                            Else
                                lstrQuery = "ProjectStatusCode ='" + ProjectStatus.CMPLD.ToString + "'"
                                larrProjectSummary = ldtProjectSummary.Select(lstrQuery)
                                If larrProjectSummary.Length > 0 Then
                                    lintSelectedRowIndex = ldtProjectSummary.Rows().IndexOf(larrProjectSummary(0))
                                Else
                                    lstrQuery = "ProjectStatusCode ='" + ProjectStatus.CNCLD.ToString + "'"
                                    larrProjectSummary = ldtProjectSummary.Select(lstrQuery)
                                    If larrProjectSummary.Length > 0 Then
                                        lintSelectedRowIndex = ldtProjectSummary.Rows().IndexOf(larrProjectSummary(0))
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
                grdProjects.ActiveRow = grdProjects.Rows(lintSelectedRowIndex)
                SetGridProperties(grdProjects)
            Else
                lblNoListing.Text = mstrNoListing
                grdProjects.Visible = False
                grdListings.Visible = False
                lblNoProject.Visible = True
                If menmScreenType = DataHubScreenType.eSource Then
                    lblNoListing.Visible = True
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            ldtProjectSummary = Nothing
            larrProjectSummary = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To get the listing summary of the selected project
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub GetListingSummary()
        Dim dtListingSummary As New DataTable
        Try
            dtListingSummary.Load(mclsUploadeSourceManager.GetProjectListings(grdProjects.ActiveRow.GetCellValue("ProjectID")))
            If dtListingSummary.Rows.Count > 0 Then
                grdListings.Visible = True
                lblNoListing.Visible = False
                grdListings.DataSource = dtListingSummary
                SetGridProperties(grdListings)
            Else
                lblNoListing.Text = mstrNoListing
                grdListings.Visible = False
                lblNoListing.Visible = True
            End If
            dtListingSummary = Nothing
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
            'Me.LogErrorToDatabase(ex)
        Finally
            dtListingSummary = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To get the source names based on the selected source type
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub GeteSourceTypeList()
        Dim ldteSourceTypeList As New DataTable
        Try
            ldteSourceTypeList.Load(mclsUploadeSourceManager.GeteSourceTypeList())
            cmbeSourceType.DataSource = ldteSourceTypeList
            If ldteSourceTypeList IsNot Nothing Then
                If ldteSourceTypeList.Rows.Count > 0 Then
                    cmbeSourceType.SelectedIndex = 0
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            ldteSourceTypeList = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To formulate the column title and listing name label setting
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub FormulateColumnTitle()
        If cmbSourceName.SelectedIndex = 0 Then
            txtColumnTitle.Text = ""
            Exit Sub
        End If
        If grdListings.ActiveRow IsNot Nothing AndAlso cmbSourceName.SelectedItem IsNot Nothing Then
            txtColumnTitle.Text = cmbSourceName.SelectedItem("Source Name")
            lblListingName.Text = grdListings.ActiveRow.GetCellValue("ListingName")
        End If
    End Sub

    ''' <summary>
    ''' To get the EDS File data/clone in a datatable
    ''' </summary>
    ''' <param name="IsColumDupliacte"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetExcelFileData(ByRef isColumDupliacte As Boolean, ByRef pdtExcel As DataTable, ByVal IsFromUpload As Boolean, ByVal ldtColMapping As DataTable) As Boolean
        Dim ColName As String = ""
        Dim ldtExcelData As New DataTable
        Dim Count As Integer
        Try
            'Populates the EDS data in datatable
            If GetDataFromExcel(isColumDupliacte, ldtExcelData, IsFromUpload, ldtColMapping) = False Then Return False

            isColumDupliacte = False

            'If coming from Upload then copy the whole data in the table, but if it's coming from Sheet/Source combo then have only the clone.
            If IsFromUpload Then
                pdtExcel = ldtExcelData.Copy()
            Else
                pdtExcel = ldtExcelData.Clone
            End If

            While Count < pdtExcel.Columns.Count
                pdtExcel.Columns(Count).ColumnName = ldtExcelData.Columns(Count).ColumnName
                Count += 1
            End While

            'If the selected sheet does not have any column then exit
            If pdtExcel.Columns.Count > 0 Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
            Return False
        Finally
            ldtExcelData = Nothing
        End Try
    End Function

    ''' <summary>
    ''' To get the data from the excel file and store in a datatable and also to validate the selected sheet
    ''' </summary>
    ''' <param name="isColumDupliacte"></param>
    ''' <param name="dtInvestor"></param>
    ''' <param name="IsFromUpload"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetDataFromExcel(ByVal isColumDupliacte As Boolean, ByRef dtInvestor As DataTable, ByVal IsFromUpload As Boolean, ByVal ldtColMapping As DataTable) As Boolean
        'Create the temporary table to store data
        Dim myDataColumn As DataColumn
        Dim myDataRow As DataRow
        Dim columnName As String

        'MIN/MAX Values used to frame the working size of the Excel data to be imported.
        Dim minCellRow As Integer = Int32.MaxValue
        Dim maxCellRow As Integer = Int32.MinValue
        Dim minCellColumn As Integer = Int32.MaxValue
        Dim maxCellColumn As Integer = Int32.MinValue

        Dim lstrColName As String = ""
        Dim lintRowCounter As Integer = 0
        Dim larrSysColumns(0) As String
        Dim lsheet As Infragistics.Documents.Excel.Worksheet = internalWorkBook.Worksheets(_SheetName)
        Dim larrPositionCol() As DataRow
        Dim lintPositionColIndex As Integer = -1
        Dim lstrPositionMapCol As String = ""

        Dim lintFilingDateIndex As Integer = -1

        Dim result As DateTime
        Dim lCultureInfo As System.Globalization.CultureInfo = Nothing
        Try
            While lintRowCounter < lsheet.Rows.Count
                'If the selected sheet contains any blank row in between valid rows, then inform the user and exit
                If lsheet.Rows(lintRowCounter).Cells.Count = 0 Then
                    Me.ShowMessageBox(DBResult.EDS_UPLD_MPNG_BLANK_ROWS)
                    Return False
                End If
                'Set the starting/ending column & row
                For Each cell As Infragistics.Documents.Excel.WorksheetCell In lsheet.Rows(lintRowCounter).Cells
                    If lsheet.Rows(lintRowCounter).GetCellValue(cell.ColumnIndex) IsNot Nothing Then
                        'Logic For Determining the Range of Rows/Columns in the Excel File.
                        minCellRow = Math.Min(minCellRow, cell.RowIndex)
                        maxCellRow = Math.Max(maxCellRow, cell.RowIndex)
                        minCellColumn = Math.Min(minCellColumn, cell.ColumnIndex)
                        maxCellColumn = Math.Max(maxCellColumn, cell.ColumnIndex)
                    End If
                Next
                lintRowCounter += 1
            End While

            If IsFromUpload Then
                'Get the index of the Position column
                larrPositionCol = ldtColMapping.Select("DBFieldName ='Position'")
                If larrPositionCol.Length = 1 Then
                    lstrPositionMapCol = larrPositionCol(0).Item("Map Field").ToString
                End If
            End If

            'If the selected sheet contains blank columns, then inform the user and exit
            For i As Integer = minCellColumn To maxCellColumn
                If String.IsNullOrEmpty(lsheet.Rows(minCellRow).GetCellValue(lsheet.Rows(minCellRow).Cells(i).ColumnIndex)) Then
                    Me.ShowMessageBox(DBResult.BLANK_COL_FILE)
                    Return False
                End If
                'Get the column name
                columnName = lsheet.Rows(minCellRow).GetCellValue(lsheet.Rows(minCellRow).Cells(i).ColumnIndex).ToString.Trim
                'The export that was demonstrated earlier utilizes the first row
                'for the column header.  We can now use that to give column names.
                If columnName.Equals(lstrPositionMapCol) AndAlso IsFromUpload Then
                    myDataColumn = New DataColumn(columnName, GetType(Int64))
                Else
                    myDataColumn = New DataColumn(columnName)
                End If
                dtInvestor.Columns.Add(myDataColumn)
            Next

            'If the selected sheet contains duplicate columns, then inform the user and exit
            If Not (CommonCLL.CheckDuplicateColumn(lstrColName, internalWorkBook, _SheetName, minCellRow, maxCellRow, minCellColumn, maxCellColumn)) Then
                Me.ShowMessageBox(DBResult.COLDUP_FILE, lstrColName)
                Return False
            End If

            'Prepare the set of system columns
            For Each lstr As String In marrExcludedFromFilters
                larrSysColumns(larrSysColumns.Length - 1) = lstr.ToUpper
                ReDim Preserve larrSysColumns(larrSysColumns.Length)
            Next

            'Add Field names to be used in Create New form  in the syscolumn list
            For Each lstr As String In marrCreateNewFields
                larrSysColumns(larrSysColumns.Length - 1) = lstr.ToUpper
                ReDim Preserve larrSysColumns(larrSysColumns.Length)
            Next

            larrSysColumns(larrSysColumns.Length - 1) = mstrMappedInvName.ToUpper
            ReDim Preserve larrSysColumns(larrSysColumns.Length)
            larrSysColumns(larrSysColumns.Length - 1) = mstrMappedFundName.ToUpper

            'If the selected sheet contains system columns, then inform the user and exit
            If Not CommonCLL.CheckSystemColumns(lstrColName, internalWorkBook, _SheetName, minCellRow, maxCellRow, minCellColumn, maxCellColumn, larrSysColumns) Then
                If lstrColName.Contains(", ") Then
                    Me.ShowMessageBox(DBResult.EDS_UPLD_MPNG_SYS_COLS, lstrColName)
                Else
                    Me.ShowMessageBox(DBResult.EDS_UPLD_MPNG_SYS_COL, lstrColName)
                End If
                Return False
            End If

            'Add a copy of the FilingDate column that will contain the formatted date and this column will update the actual Filing Date column while doing import
            dtInvestor.Columns.Add("FilingDateCopy", GetType(String))

            'Prepare the datatable with excel rows
            If isColumDupliacte = False Then
                For rowIndex As Integer = minCellRow + 1 To maxCellRow
                    'Create a new DataRow
                    myDataRow = dtInvestor.NewRow()
                    'Loop through the columns and associate the value to each cell
                    For columnIndex As Integer = minCellColumn To maxCellColumn
                        If lsheet.Rows(rowIndex).GetCellValue(lsheet.Rows(rowIndex).Cells(columnIndex).ColumnIndex) Is Nothing Then
                            myDataRow(columnIndex) = DBNull.Value
                        Else
                            myDataRow(columnIndex) = lsheet.Rows(rowIndex).GetCellValue(lsheet.Rows(rowIndex).Cells(columnIndex).ColumnIndex)
                        End If
                    Next
                    'Add The Row to a DataTable
                    dtInvestor.Rows.Add(myDataRow)
                Next
            End If

            If IsFromUpload Then

                'Get the index of the Position column
                larrPositionCol = ldtColMapping.Select("DBFieldName ='Position'")
                If larrPositionCol.Length = 1 Then
                    If String.IsNullOrEmpty(larrPositionCol(0).Item("Map Field").ToString) = False Then
                        lintPositionColIndex = dtInvestor.Columns(larrPositionCol(0).Item("Map Field").ToString).Ordinal
                    End If
                End If

                'Dim supportedFormats() As String = New String() {"dd-MM-yyyy", "dd-MM-yy", "dd-MM-yy HH:mm:ss tt", "dd-MM-yyyy HH:mm:ss tt", "dd-MMM-yy", "dd-MMM-yyyy HH:mm:ss tt", "dd-MMM-yy HH:mm:ss tt", "dd/MM/yyyy", "dd/MM/yy", "dd/MM/yy HH:mm:ss tt", "dd/MM/yyyy HH:mm:ss tt", "dd/MMM/yy", "dd/MMM/yyyy HH:mm:ss tt", "dd/MMM/yy HH:mm:ss tt", _
                '                                             "MM-dd-yyyy", "MM-dd-yy", "MM-dd-yy HH:mm:ss tt", "MM-dd-yyyy HH:mm:ss tt", "MMM-dd-yy", "MMM-dd-yyyy HH:mm:ss tt", "M-dd-yyyy HH:mm:ss tt", "mm-dd-yyyy HH:mm:ss tt", "MMM-dd-yy HH:mm:ss tt", "MM/dd/yyyy", "MM/dd/yy", "MM/dd/yy HH:mm:ss tt", "MM/dd/yyyy HH:mm:ss tt", "MMM/dd/yy", "MMM/dd/yyyy HH:mm:ss tt", "MMM/dd/yy HH:mm:ss tt", _
                '                                             "d/MM/yyyy HH:mm:ss tt", "MM/d/yyyy HH:mm:ss tt", "M/dd/yyyy HH:mm:ss tt", "dd/M/yyyy HH:mm:ss tt", "d/MM/yyyy", "MM/d/yyyy", "M/dd/yyyy", "dd/M/yyyy", "d-MM-yyyy HH:mm:ss tt", "MM-d-yyyy HH:mm:ss tt", "M-dd-yyyy HH:mm:ss tt", "dd-M-yyyy HH:mm:ss tt", "d-MM-yyyy", "MM-d-yyyy", "M-dd-yyyy", "dd-M-yyyy", _
                '                                             "d/M/yyyy HH:mm:ss tt", "M/d/yyyy HH:mm:ss tt", "d/M/yyyy", "M/d/yyyy", "d-M-yyyy HH:mm:ss tt", "M-d-yyyy HH:mm:ss tt", "d-M-yyyy", "M-d-yyyy"}
                


                If cmbeSourceType.SelectedItem("SourceTypeCode") = SourceTypeCode.PBC.ToString Then
                    'Get the index of the FilingDate column
                    larrPositionCol = ldtColMapping.Select("DBFieldName ='FilingDate'")
                    If larrPositionCol.Length = 1 Then
                        If String.IsNullOrEmpty(larrPositionCol(0).Item("Map Field").ToString) = False Then
                            lintFilingDateIndex = dtInvestor.Columns(larrPositionCol(0).Item("Map Field").ToString).Ordinal
                        End If
                    End If

                    Dim lenmDateFormatStatus As DateFormatStatus
                    Dim lintFirstValidFilingDateRow As Integer

                    'If FilingDate column exists, then do the relevant settings
                    If lintFilingDateIndex <> -1 Then
                        Try
                            'Get the row index of first valid filing date to get the culture info and date format of Filing Date
                            For rowIndex As Integer = 0 To dtInvestor.Rows.Count - 1
                                If dtInvestor.Rows(rowIndex).Item(lintFilingDateIndex) IsNot DBNull.Value Then
                                    lintFirstValidFilingDateRow = rowIndex
                                    Exit For
                                End If
                            Next
                            'If the selected culture is UK
                            result = DateTime.ParseExact(dtInvestor.Rows(lintFirstValidFilingDateRow).Item(lintFilingDateIndex).ToString, supportedFormats, System.Globalization.CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB"), System.Globalization.DateTimeStyles.None)
                            lenmDateFormatStatus = DateFormatStatus.UK
                        Catch ex As Exception
                            Try
                                result = DateTime.ParseExact(dtInvestor.Rows(lintFirstValidFilingDateRow).Item(lintFilingDateIndex).ToString, supportedFormats, System.Globalization.CultureInfo.GetCultureInfoByIetfLanguageTag("en-US"), System.Globalization.DateTimeStyles.None)
                                'If the selected culture is US
                                lenmDateFormatStatus = DateFormatStatus.US
                            Catch ex1 As Exception
                                lenmDateFormatStatus = DateFormatStatus.OTHERS
                                ShowMessageBox(DBResult.EDS_UPLD_FLNG_DATE_OTHERS)
                                Return False
                            End Try
                        End Try
                        If lenmDateFormatStatus = DateFormatStatus.UK Then
                            lCultureInfo = New System.Globalization.CultureInfo("en-GB")
                        ElseIf lenmDateFormatStatus = DateFormatStatus.US Then
                            lCultureInfo = New System.Globalization.CultureInfo("en-US")
                        End If
                    End If
                End If
                If lintPositionColIndex >= 0 Then
                    For rowIndex As Integer = 0 To dtInvestor.Rows.Count - 1
                        'If the position column is non-numeric, inform the user and exit
                        If IsNumeric(dtInvestor.Rows(rowIndex).Item(lintPositionColIndex)) = False AndAlso (dtInvestor.Rows(rowIndex).Item(lintPositionColIndex) IsNot DBNull.Value) AndAlso (dtInvestor.Rows(rowIndex).Item(lintPositionColIndex).ToString.Equals("") = False) OrElse (IsNumeric(dtInvestor.Rows(rowIndex).Item(lintPositionColIndex)) AndAlso dtInvestor.Rows(rowIndex).Item(lintPositionColIndex).ToString.Contains(".")) Then
                            Me.ShowMessageBox(DBResult.EDS_UPLD_POSITION_NONUMERIC)
                            Return False
                        ElseIf (dtInvestor.Rows(rowIndex).Item(lintPositionColIndex).ToString.Contains("-")) Then
                            Me.ShowMessageBox(DBResult.EDS_UPLD_POSITION_NEGATIVE)
                            Return False
                        End If
                        'If Filing Date column exists
                        If lintFilingDateIndex <> -1 Then
                            Try
                                If dtInvestor.Rows(rowIndex).Item(lintFilingDateIndex) Is DBNull.Value Then
                                    'If the Filing Date is null, then do nothing
                                    Continue For
                                End If

                                'If Filing Date is a valid date, then update the FilingDateCopy column

                                result = DateTime.ParseExact(dtInvestor.Rows(rowIndex).Item(lintFilingDateIndex).ToString, supportedFormats, lCultureInfo, System.Globalization.DateTimeStyles.None)
                                'dtInvestor.Rows(rowIndex).Item(lintFilingDateIndex) = Format(result, "MMM-dd-yy")
                                dtInvestor.Rows(rowIndex).Item("FilingDateCopy") = Format(result, "MM-dd-yy")
                                'dtInvestor.Rows(rowIndex).Item("FilingDateCopy") = Format(result, "dd-MM-yy")
                                'dtInvestor.Rows(rowIndex).Item("FilingDateCopy") = result
                            Catch ex As Exception
                                ShowMessageBox(DBResult.EDS_UPLD_FLNG_DATE_OTHERS)
                                Return False
                            End Try
                        End If
                    Next
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
            Return False
        Finally
            myDataColumn = Nothing
            myDataRow = Nothing
            lCultureInfo = Nothing
        End Try
        Return True
    End Function

    'Private Function ValidateFilingDate() As Boolean
    '    Dim supportedFormats() As String = New String() {"dd-MM-yyyy", "dd-MM-yy", "dd-MM-yy HH:mm:ss tt", "dd-MM-yyyy HH:mm:ss tt", "dd-MMM-yy", "dd-MMM-yyyy HH:mm:ss tt", "dd-MMM-yy HH:mm:ss tt", "dd/MM/yyyy", "dd/MM/yy", "dd/MM/yy HH:mm:ss tt", "dd/MM/yyyy HH:mm:ss tt", "dd/MMM/yy", "dd/MMM/yyyy HH:mm:ss tt", "dd/MMM/yy HH:mm:ss tt", _
    '                                                         "MM-dd-yyyy", "MM-dd-yy", "MM-dd-yy HH:mm:ss tt", "MM-dd-yyyy HH:mm:ss tt", "MMM-dd-yy", "MMM-dd-yyyy HH:mm:ss tt", "M-dd-yyyy HH:mm:ss tt", "mm-dd-yyyy HH:mm:ss tt", "MMM-dd-yy HH:mm:ss tt", "MM/dd/yyyy", "MM/dd/yy", "MM/dd/yy HH:mm:ss tt", "MM/dd/yyyy HH:mm:ss tt", "MMM/dd/yy", "MMM/dd/yyyy HH:mm:ss tt", "MMM/dd/yy HH:mm:ss tt", _
    '                                                         "d/MM/yyyy HH:mm:ss tt", "MM/d/yyyy HH:mm:ss tt", "M/dd/yyyy HH:mm:ss tt", "dd/M/yyyy HH:mm:ss tt", "d/MM/yyyy", "MM/d/yyyy", "M/dd/yyyy", "dd/M/yyyy", "d-MM-yyyy HH:mm:ss tt", "MM-d-yyyy HH:mm:ss tt", "M-dd-yyyy HH:mm:ss tt", "dd-M-yyyy HH:mm:ss tt", "d-MM-yyyy", "MM-d-yyyy", "M-dd-yyyy", "dd-M-yyyy", _
    '                                                         "d/M/yyyy HH:mm:ss tt", "M/d/yyyy HH:mm:ss tt", "d/M/yyyy", "M/d/yyyy", "d-M-yyyy HH:mm:ss tt", "M-d-yyyy HH:mm:ss tt", "d-M-yyyy", "M-d-yyyy"}
    '    '*********************************************************
    '    Dim result As DateTime
    '    Dim lenmDateFormatStatus As DateFormatStatus
    '    If DataTable.Rows(0).Item("ReportDate") IsNot DBNull.Value Then
    '        Try
    '            result = DateTime.ParseExact(DataTable.Rows(0).Item("ReportDate").ToString, supportedFormats, System.Globalization.CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB"), System.Globalization.DateTimeStyles.None)
    '            'result = DateTime.ParseExact(lRow.Item("ReportDate").ToString, supportedFormats, System.Globalization.CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB"), System.Globalization.DateTimeStyles.None)
    '            lenmDateFormatStatus = DateFormatStatus.UK
    '        Catch ex As Exception
    '            Try
    '                result = DateTime.ParseExact(DataTable.Rows(0).Item("ReportDate").ToString, supportedFormats, System.Globalization.CultureInfo.GetCultureInfoByIetfLanguageTag("en-US"), System.Globalization.DateTimeStyles.None)
    '                lenmDateFormatStatus = DateFormatStatus.US
    '            Catch ex1 As Exception
    '                lenmDateFormatStatus = DateFormatStatus.OTHERS
    '                pstrWarningMsg = "Report Date column is not in [dd/MM/yy]/[MM/dd/yy] format. Please correct the file and try again."
    '                'pblnIsCorrectDateFormat = False
    '                Return Nothing
    '            End Try
    '        End Try
    '    End If

    '    Dim lCultureInfo As System.Globalization.CultureInfo = Nothing
    '    If lenmDateFormatStatus = DateFormatStatus.UK Then
    '        lCultureInfo = New System.Globalization.CultureInfo("en-GB")
    '    ElseIf lenmDateFormatStatus = DateFormatStatus.US Then
    '        lCultureInfo = New System.Globalization.CultureInfo("en-US")
    '    End If
    '    For Each lRow As DataRow In DataTable.Rows
    '        Try
    '            If lRow.Item("ReportDate") Is DBNull.Value Then
    '                Continue For
    '                'If lRow.Item("HolderName").ToString.Trim = "" Then
    '                '    Exit For
    '                'Else
    '                '    pstrWarningMsg = "Report Date column is not in [dd/MM/yy]/[MM/dd/yy] format. Please correct the file and try again."
    '                '    Return Nothing
    '                'End If
    '            End If
    '            result = DateTime.ParseExact(lRow.Item("ReportDate").ToString, supportedFormats, lCultureInfo, System.Globalization.DateTimeStyles.None)
    '            'result = DateTime.ParseExact(lRow.Item("ReportDate").ToString, supportedFormats, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.None)
    '            'result = DateTime.ParseExact(lRow.Item("ReportDate").ToString, supportedFormats, System.Globalization.CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB"), System.Globalization.DateTimeStyles.None)
    '            lRow.Item("ReportDate") = Format(result, "MMM-dd-yy")
    '        Catch ex As Exception
    '            'pblnIsCorrectDateFormat = False
    '            pstrWarningMsg = "Report Date column is not in [dd/MM/yy]/[MM/dd/yy] format. Please correct the file and try again."
    '            Return Nothing
    '        End Try
    '    Next
    '    Return True
    'End Function

    ''' <summary>
    ''' To get the template mapping for the selected template
    ''' </summary>
    ''' <param name="IsFromUpload"></param>
    ''' <remarks></remarks>
    Private Sub GetTemplateMapping(ByVal IsFromUpload As Boolean)
        If cmbSheets.SelectedItem Is Nothing Then
            'txtColumnTitle.Text = ""
            Exit Sub
        End If
        Dim IsColumDupliacte As Boolean = True
        Dim ExcelColumns As New DataTable
        Dim cntrlTemplateMapping As UCTemplateMapping = Nothing
        Try
            If cmbSheets.SelectedItem("SNo.") = CommonCLL.m_strSelectIDValue Then
                'txtColumnTitle.Text = ""
                Exit Sub
            Else
                _SheetName = cmbSheets.SelectedItem("Name")
                'Get excel data and run the validations
                If Not GetExcelFileData(IsColumDupliacte, ExcelColumns, IsFromUpload, Nothing) Then
                    Exit Sub
                End If

                'If Template Mapping User control is not added yet, the create new instance and add it to the panel
                If pnlTemplateMapping.Controls.Count = 0 Then
                    cntrlTemplateMapping = New UCTemplateMapping(True, mstrConnectionstring)
                    cntrlTemplateMapping.SourceID = cmbSourceName.SelectedValue
                    cntrlTemplateMapping.FormatID = 0
                    'cntrlTemplateMapping.enmSourceTypeCode = DirectCast(cmbeSourceType.SelectedItem("SourceTypeID"), SourceTypeCode)
                    cntrlTemplateMapping.enmSourceTypeCode = DirectCast([Enum].Parse(GetType(SourceTypeCode), cmbeSourceType.SelectedItem("SourceTypeCode")), SourceTypeCode)

                    lblMatchColumns.Visible = True
                    cntrlTemplateMapping.mdtExcelClone = ExcelColumns
                    pnlTemplateMapping.Controls.Clear()
                    pnlTemplateMapping.Height = cntrlTemplateMapping.Height + 200
                    cntrlTemplateMapping.Height = 500
                    pnlTemplateMapping.Controls.Add(cntrlTemplateMapping)
                Else
                    'Update the TemplateMapping's properties and refresh the user control
                    For Each lControl As Control In pnlTemplateMapping.Controls
                        If lControl.Name.Equals("UCTemplateMapping") Then
                            Dim lcntrlTemplateMapping As UCTemplateMapping
                            lcntrlTemplateMapping = CType(lControl, UCTemplateMapping)
                            lcntrlTemplateMapping.SourceID = cmbSourceName.SelectedValue
                            lcntrlTemplateMapping.mdtExcelClone = ExcelColumns
                            lcntrlTemplateMapping.FormatID = 0
                            lcntrlTemplateMapping.isChanged = False
                            lcntrlTemplateMapping.enmSourceTypeCode = DirectCast([Enum].Parse(GetType(SourceTypeCode), cmbeSourceType.SelectedItem("SourceTypeCode")), SourceTypeCode)
                            'lcntrlTemplateMapping.enmSourceTypeCode = DirectCast(cmbeSourceType.SelectedItem("SourceTypeID"), SourceTypeCode)
                            lcntrlTemplateMapping.CallOnLoad()
                            Exit For
                        End If
                    Next
                End If
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            ExcelColumns = Nothing
            cntrlTemplateMapping = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' 'To Select Sheet Names of the selected Investor Excel file
    ''' </summary>
    ''' <param name="File"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetWorksheetNames(ByVal file As String) As Boolean
        Dim ReturnVal As Boolean = False
        Dim WorkBooks As New DataTable
        Dim WorkBook As DataRow
        Try
            internalWorkBook = Infragistics.Documents.Excel.Workbook.Load(txtAttach.Text, False)
            WorkBooks.Columns.Add("SNo.", GetType(Integer))
            WorkBooks.Columns.Add("Name", GetType(String))
            WorkBook = WorkBooks.NewRow()
            WorkBook("SNo.") = ContentManager.GetContent("SELECT_VAL")
            WorkBook("Name") = ContentManager.GetContent("SELECT_TEXT")
            WorkBooks.Rows.InsertAt(WorkBook, 0)
            If internalWorkBook.Worksheets.Count > 0 Then
                For i As Integer = 0 To internalWorkBook.Worksheets.Count - 1
                    WorkBook = WorkBooks.NewRow()
                    WorkBook("SNo.") = i + 1
                    WorkBook("Name") = internalWorkBook.Worksheets(i).Name
                    WorkBooks.Rows.Add(WorkBook)
                    WorkBook = WorkBooks.NewRow()
                Next
            End If
            'internalWorkBook = Nothing
            If CommonCLL.CheckEmptyDataTable(WorkBooks) Then
                ReturnVal = False
            Else
                cmbSheets.Enabled = True
                cmbSheets.DataSource = WorkBooks
                If WorkBooks.Rows.Count > 0 Then
                    cmbSheets.SelectedIndex = 1
                End If
                ReturnVal = True
            End If

        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
            ReturnVal = False
        Finally
            WorkBooks = Nothing
        End Try
        Return ReturnVal
    End Function

    ''' <summary>
    ''' Set the Mapped Investor's Index
    ''' </summary>
    ''' <param name="ldtExcelData"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <param name="lintOrdinal"></param>
    ''' <param name="lstrColName"></param>
    ''' <remarks></remarks>
    Private Sub GetMappedInvestorOrdinal(ByRef ldtExcelData As DataTable, ByRef ldtColMapping As DataTable, ByRef lintOrdinal As Integer, ByRef lstrColName As String)
        Dim lstrQuery As String
        Dim larrMappedCol() As DataRow
        Try
            lstrQuery = "DBFieldName ='" + RegMapColumns.MappedInvestorName.ToString + "'"
            larrMappedCol = ldtColMapping.Select(lstrQuery)
            If larrMappedCol.Length = 1 AndAlso larrMappedCol(0).Item("Map Field").ToString <> "" Then
                lintOrdinal = 0
                lstrColName = larrMappedCol(0).Item("Map Field")
                ldtExcelData.Columns(lstrColName).SetOrdinal(lintOrdinal)
            Else
                lintOrdinal = -1
            End If
        Finally
            larrMappedCol = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Set the Mapped Fund's Index
    ''' </summary>
    ''' <param name="ldtExcelData"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <param name="lintOrdinal"></param>
    ''' <param name="lstrColName"></param>
    ''' <remarks></remarks>
    Private Sub GetMappedFundOrdinal(ByRef ldtExcelData As DataTable, ByRef ldtColMapping As DataTable, ByRef lintOrdinal As Integer, ByRef lstrColName As String)
        Dim lstrQuery As String
        Dim larrMappedCol() As DataRow
        Try
            GetMappedInvestorOrdinal(ldtExcelData, ldtColMapping, lintOrdinal, lstrColName)
            lstrQuery = "DBFieldName ='" + RegMapColumns.MappedFundName.ToString + "'"
            larrMappedCol = ldtColMapping.Select(lstrQuery)
            If larrMappedCol.Length = 1 AndAlso larrMappedCol(0).Item("Map Field").ToString <> "" Then
                lintOrdinal += 1
                lstrColName = larrMappedCol(0).Item("Map Field")
                ldtExcelData.Columns(lstrColName).SetOrdinal(lintOrdinal)
            Else
                lintOrdinal = -1
            End If
        Finally
            larrMappedCol = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Set the Match Column's Index
    ''' </summary>
    ''' <param name="ldtExcelData"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <param name="lintOrdinal"></param>
    ''' <param name="lstrColName"></param>
    ''' <remarks></remarks>
    Private Sub GetMappedMatchCol1Ordinal(ByRef ldtExcelData As DataTable, ByRef ldtColMapping As DataTable, ByRef lintOrdinal As Integer, ByRef lstrColName As String)
        Dim lstrQuery As String
        Dim larrMappedCol() As DataRow
        Try
            GetMappedFundOrdinal(ldtExcelData, ldtColMapping, lintOrdinal, lstrColName)
            lstrQuery = "DBFieldName ='" + RegMapColumns.MatchCol1.ToString + "'"
            larrMappedCol = ldtColMapping.Select(lstrQuery)
            If larrMappedCol.Length = 1 AndAlso larrMappedCol(0).Item("Map Field").ToString <> "" Then
                lintOrdinal += 1
                lstrColName = larrMappedCol(0).Item("Map Field")
                ldtExcelData.Columns(lstrColName).SetOrdinal(lintOrdinal)
            Else
                lintOrdinal = -1
            End If
        Finally
            larrMappedCol = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Set the Match Column's Index
    ''' </summary>
    ''' <param name="ldtExcelData"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <param name="lintOrdinal"></param>
    ''' <param name="lstrColName"></param>
    ''' <remarks></remarks>
    Private Sub GetMappedMatchCol2Ordinal(ByRef ldtExcelData As DataTable, ByRef ldtColMapping As DataTable, ByRef lintOrdinal As Integer, ByRef lstrColName As String)
        Dim lstrQuery As String
        Dim larrMappedCol() As DataRow
        Try
            GetMappedMatchCol1Ordinal(ldtExcelData, ldtColMapping, lintOrdinal, lstrColName)
            lstrQuery = "DBFieldName ='" + RegMapColumns.MatchCol2.ToString + "'"
            larrMappedCol = ldtColMapping.Select(lstrQuery)
            If larrMappedCol.Length = 1 AndAlso larrMappedCol(0).Item("Map Field").ToString <> "" Then
                lintOrdinal += 1
                lstrColName = larrMappedCol(0).Item("Map Field")
                ldtExcelData.Columns(lstrColName).SetOrdinal(lintOrdinal)
            Else
                lintOrdinal = -1
            End If
            'Catch ex As Exception
            '    Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            larrMappedCol = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Set the Match Column's Index
    ''' </summary>
    ''' <param name="ldtExcelData"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <param name="lintOrdinal"></param>
    ''' <param name="lstrColName"></param>
    ''' <remarks></remarks>
    Private Sub GetMappedMatchCol3Ordinal(ByRef ldtExcelData As DataTable, ByRef ldtColMapping As DataTable, ByRef lintOrdinal As Integer, ByRef lstrColName As String)
        Dim lstrQuery As String
        Dim larrMappedCol() As DataRow
        Try
            GetMappedMatchCol2Ordinal(ldtExcelData, ldtColMapping, lintOrdinal, lstrColName)
            lstrQuery = "DBFieldName ='" + RegMapColumns.MatchCol3.ToString + "'"
            larrMappedCol = ldtColMapping.Select(lstrQuery)
            If larrMappedCol.Length = 1 AndAlso larrMappedCol(0).Item("Map Field").ToString <> "" Then
                lintOrdinal += 1
                lstrColName = larrMappedCol(0).Item("Map Field")
                ldtExcelData.Columns(lstrColName).SetOrdinal(lintOrdinal)
            Else
                lintOrdinal = -1
            End If
            'Catch ex As Exception
            '    Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            larrMappedCol = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Set the Match Column's Index
    ''' </summary>
    ''' <param name="ldtExcelData"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <param name="lintOrdinal"></param>
    ''' <param name="lstrColName"></param>
    ''' <remarks></remarks>
    Private Sub GetMappedMatchCol4Ordinal(ByRef ldtExcelData As DataTable, ByRef ldtColMapping As DataTable, ByRef lintOrdinal As Integer, ByRef lstrColName As String)
        Dim lstrQuery As String
        Dim larrMappedCol() As DataRow
        Try
            GetMappedMatchCol3Ordinal(ldtExcelData, ldtColMapping, lintOrdinal, lstrColName)
            lstrQuery = "DBFieldName ='" + RegMapColumns.MatchCol4.ToString + "'"
            larrMappedCol = ldtColMapping.Select(lstrQuery)
            If larrMappedCol.Length = 1 AndAlso larrMappedCol(0).Item("Map Field").ToString <> "" Then
                lintOrdinal += 1
                lstrColName = larrMappedCol(0).Item("Map Field")
                ldtExcelData.Columns(lstrColName).SetOrdinal(lintOrdinal)
            Else
                lintOrdinal = -1
            End If
            'Catch ex As Exception
            '    Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            larrMappedCol = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Set the Match Column's Index
    ''' </summary>
    ''' <param name="ldtExcelData"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <param name="lintOrdinal"></param>
    ''' <param name="lstrColName"></param>
    ''' <remarks></remarks>
    Private Sub GetMappedMatchCol5Ordinal(ByRef ldtExcelData As DataTable, ByRef ldtColMapping As DataTable, ByRef lintOrdinal As Integer, ByRef lstrColName As String)
        Dim lstrQuery As String
        Dim larrMappedCol() As DataRow
        Try
            GetMappedMatchCol4Ordinal(ldtExcelData, ldtColMapping, lintOrdinal, lstrColName)
            lstrQuery = "DBFieldName ='" + RegMapColumns.MatchCol5.ToString + "'"
            larrMappedCol = ldtColMapping.Select(lstrQuery)
            If larrMappedCol.Length = 1 AndAlso larrMappedCol(0).Item("Map Field").ToString <> "" Then
                lintOrdinal += 1
                lstrColName = larrMappedCol(0).Item("Map Field")
                ldtExcelData.Columns(lstrColName).SetOrdinal(lintOrdinal)
            Else
                lintOrdinal = -1
            End If
            'Catch ex As Exception
            '    Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            larrMappedCol = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Set the Position Column's Index
    ''' </summary>
    ''' <param name="ldtExcelData"></param>
    ''' <param name="ldtColMapping"></param>
    ''' <param name="lintOrdinal"></param>
    ''' <param name="lstrColName"></param>
    ''' <remarks></remarks>
    Private Sub GetMappedPositionOrdinal(ByRef ldtExcelData As DataTable, ByRef ldtColMapping As DataTable, ByRef lintOrdinal As Integer, ByRef lstrColName As String)
        Dim lstrQuery As String
        Dim larrMappedCol() As DataRow
        Try
            GetMappedMatchCol5Ordinal(ldtExcelData, ldtColMapping, lintOrdinal, lstrColName)
            lstrQuery = "DBFieldName ='" + RegMapColumns.Position.ToString + "'"
            larrMappedCol = ldtColMapping.Select(lstrQuery)
            If larrMappedCol.Length = 1 AndAlso larrMappedCol(0).Item("Map Field").ToString <> "" Then
                lintOrdinal += 1
                lstrColName = larrMappedCol(0).Item("Map Field")
                ldtExcelData.Columns(lstrColName).SetOrdinal(lintOrdinal)
            Else
                lintOrdinal = -1
            End If
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
        Finally
            larrMappedCol = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Check if the selected project is valid for selection
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNextValidProject() As Boolean
        Dim ldtValidProject As New DataTable
        Try
            ldtValidProject.Load(mclsUploadeSourceManager.GetNextValidProject(grdProjects.ActiveRow.GetCellValue("ProjectID")))
            If ldtValidProject IsNot Nothing Then
                'If no future project has gone beyond the DR Sent stage: ‘Please consider selecting DD MMM YYYY project instead or change the status for this project first’. The project referred to in the message should be the next available DE Started or Finalising List project.
                If ldtValidProject.Rows.Count = 0 Then
                    ShowMessageBox(DBResult.EDS_UPLD_CNCL_PRJ_NOPROJECT)
                Else
                    'In the event that some future project has gone beyond the DR Sent stage: ‘Please consider selecting DD MMM YYYY project instead’. 
                    'The project referred to in the message should be the next available DE Started or Finalising List project.
                    If ldtValidProject.Rows(0).Item("IsFuture") Then
                        'If the selected project is a cancelled project
                        ShowMessageBox(DBResult.EDS_UPLD_CNCL_PRJ_FUTREPROJECT, ldtValidProject.Rows(0).Item("ProjectName"))
                    Else
                        ShowMessageBox(DBResult.EDS_UPLD_CNCL_PRJ_PASTPROJECT, ldtValidProject.Rows(0).Item("ProjectName"))
                    End If
                End If
                Return False
            End If
            ldtValidProject = Nothing
            Return True
        Catch ex As Exception
            Me.FormulateAndLogError(ex, mstrScreenName, mintProjectID)
            Return False
        Finally
            ldtValidProject = Nothing
        End Try
    End Function

#End Region

End Class